Union#
The UNION clause allows us to combine the results from several queries together. The clause doesn’t join the table but merely clubs the two results together.
Syntax#
<Query1>
UNION
<Query2>
Connect to the terminal below by clicking in the widget. Once connected, the command line prompt will show up. Enter or copy and paste the command ./DataJek/Lessons/27lesson.sh and wait for the MySQL prompt to start-up.
-
As a contrived example, we’ll write a query that prints all the first names from the Actors table and all the URLs from the DigitalAssets table.
SELECT FirstName FROM Actors
UNION
SELECT URL FROM DigitalAssets;
- A more realistic example would be a query where you are required to print the top two richest actors and the least two richest.
(SELECT CONCAT(FirstName, ' ', SecondName) AS "Actor Name"
FROM Actors
ORDER BY NetworthInMillions DESC
LIMIT 2)
UNION
(SELECT CONCAT(FirstName, ' ', SecondName) AS "ThisAliasIsIgnored"
FROM Actors
ORDER BY NetworthInMillions ASC
LIMIT 2);
Note we have used the various techniques learned so far to enhance the above query. We use the concat function to join first and second names with a space separating them and use the alias “Actor Name” for the resulting column. The alias from the second query is ignored. Furthermore, we wrap the two queries in parentheses which is a requirement when using the order by or limit clause in subqueries of a union query.
-
When using the UNION clause, the two result sets being combined should have the same number and order of columns. The columns from the result sets should be of the same type or types that are compatible. For instance, the following query will error out:
SELECT FirstName, Id FROM Actors
UNION
SELECT FirstName FROM Actors;
To make the above query work, we can insert a fake column or null as follows:
SELECT FirstName, Id FROM Actors
UNION
SELECT FirstName, null FROM Actors;
-
Observe the output of the following query:
SELECT MaritalStatus FROM Actors
UNION
SELECT Gender FROM Actors;
Note that the union clause doesn’t output duplicate values and works similarly to the distinct clause. If we want duplicate values to be included in the query result, we need to use the UNION ALL clause as follows:
SELECT MaritalStatus FROM Actors
UNION ALL
SELECT Gender FROM Actors;
-
Another quirk of the UNION clause is that it may ignore the ORDER BY clause when used without the LIMIT clause in a subquery. Consider the below query:
(SELECT CONCAT(FirstName, ' ', SecondName) AS "Actor Name"
FROM Actors
ORDER BY NetworthInMillions DESC LIMIT 2)
UNION
(SELECT NetworthInMillions
FROM Actors
ORDER BY NetworthInMillions ASC);
Note that in the second subquery we order the rows in an ascending fashion by the column NetworthInMillions. The values in the result set are, however, printed disregarding the order. When we tack on a LIMIT clause to the second subquery the IDs show up in descending order as shown below:
(SELECT CONCAT(FirstName, ' ', SecondName) AS "Actor Name"
FROM Actors
ORDER BY NetworthInMillions DESC LIMIT 2)
UNION
(SELECT NetworthInMillions
FROM Actors
ORDER BY NetworthInMillions ASC LIMIT 3);
The astute reader would also notice that the types of the two columns in the result set aren’t the same. The query works because MySQL converts the int to varchar.
